/*
	This Procedure loads all the Running, Hold and Shipped Lots Inventory in Chiptrac.

 	EXEC sp_board_kit_inventory_summary_report_proc '0','0','All Vendors','0','0','0','0'
	EXEC sp_board_kit_inventory_summary_report_proc 0,'0','All Vendors','0','0','DETAIL','0'
	
sp_help lot_table
*/

create  PROC sp_board_kit_inventory_summary_report_proc

	@PRODUCT_ID 	VARCHAR(500),
	@LOT_CATEGORY	VARCHAR(50),
	@VENDOR		VARCHAR(50),
	@STATUS		VARCHAR(50),
	@ASOFDATE	VARCHAR(50),
	@REPORT_TYPE	VARCHAR(50),
	@DEFAULT_PROMPT	VARCHAR(50)
AS
BEGIN

CREATE TABLE #INVENTORY_SUMMARY_TABLE ( LOT_ID int,FAB_LOT_NUMBER VARCHAR(50),LOT_NUMBER VARCHAR(50),QTY_IN INT,
					LOT_CATEGORY VARCHAR(50),LOT_STATUS VARCHAR(50),COMMENTS VARCHAR(200),
					PART_NUMBER VARCHAR(50), PROCESSSTEP_ID INT,P_TYPE VARCHAR(50),
					PS_TYPE VARCHAR(50),VENDOR_NAME VARCHAR(50) )

DECLARE @RESULTS TABLE ( LOT_ID INT,FAB_LOT_NUMBER VARCHAR(50),LOT_NUMBER VARCHAR(50),PART_NUMBER VARCHAR(50),
			 COMMENTS VARCHAR(200),P_TYPE VARCHAR(50),
			DIE_BANK_QTY INT,ASSEMBLY_QTY INT,TEST_QTY INT,FINISHED_GOODS_QTY INT )

DECLARE @SQLSTRING 		NVARCHAR(4000)

DECLARE @PART_NUMBER 		VARCHAR(50)
DECLARE @PS_TYPE 		VARCHAR(50)
DECLARE @QTY_IN 		INT

DECLARE @DIE_BANK_QTY 		INT
DECLARE @ASSEMBLY_QTY 		INT
DECLARE @TEST_FPO		INT
DECLARE @TEST_FT		INT
DECLARE @FINISHED_GOODS_QTY 	INT
DECLARE @LOT_ID			INT
DECLARE @FAB_LOT_NUMBER		VARCHAR(50)
DECLARE	@VENDOR_LOT_NUMBER	VARCHAR(50)
DECLARE @P_TYPE			VARCHAR(50)
DECLARE @COMMENTS		VARCHAR(200)

DECLARE @TEMP_VARCHAR 	VARCHAR(50)
DECLARE @START_DATE 	DATETIME
DECLARE @END_DATE 	DATETIME

DECLARE @LOT_CURSOR 		CURSOR


	SET @SQLSTRING = N'INSERT INTO #INVENTORY_SUMMARY_TABLE
		SELECT 
			LT.LOT_ID,FAB_LOT_NUMBER, LOT_NUMBER,QTY_IN,LOT_CATEGORY,
			(CASE 
				WHEN LOT_CURRENT_STATUS = ''RUNNING'' THEN ''ACTIVE''
				ELSE ''HOLD''
			END),
			LOT_COMMENTS,PART_NUMBER,P.PROCESSSTEP_ID,P_TYPE,PS_TYPE,L_VENDOR_NAME '
	
	IF @ASOFDATE = '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + '	FROM LOT_TABLE LT,PRODUCT P, PROCESS_STEP PS, LOCATION L '
	END
	ELSE
	BEGIN
		SET @SQLSTRING = @SQLSTRING + '	FROM LOT_TABLE_HISTORY LT,PRODUCT P, PROCESS_STEP PS, LOCATION L '

		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 12:00AM'
		SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
		SET @TEMP_VARCHAR = NULL
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 11:59PM'
		SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
	END
		SET @SQLSTRING = @SQLSTRING + ' WHERE 
		LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
		AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
		AND LT.LOCATION_ID = L.LOCATION_ID
		AND ( P_TYPE = ''Board'' or p_type = ''Kit'' )
		AND P.OBSOLETE != 0 '

	IF @ASOFDATE <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.TimeStamp = ( SELECT MAX(LTH.Timestamp) FROM LOT_TABLE_HISTORY LTH WHERE lth.lot_id = lt.lot_id and LTH.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''') '
	END

	IF @PRODUCT_ID <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND PART_NUMBER IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @PRODUCT_ID +'''))'
	END
	IF @LOT_CATEGORY <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND ISNULL(LT.LOT_CATEGORY, ''PROD'') =  ''' +  @LOT_CATEGORY + '''' 
		--SET @SQLSTRING = @SQLSTRING + ' AND LT.LOT_CATEGORY =  ''' +  @LOT_CATEGORY + '''' 
	END 
	IF @VENDOR <> 'All Vendors'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND L.L_VENDOR_NAME =  ''' +  @VENDOR + '''' 
	END
	IF @STATUS <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.LOT_CURRENT_STATUS =  ''' +  @STATUS + '''' 
	END
	ELSE
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' )' 
	END	
		
	--PRINT @SQLSTRING
		
	EXEC(@SQLSTRING)
	
	
	SET @LOT_CURSOR = CURSOR FOR 

			SELECT 
				LOT_ID,FAB_LOT_NUMBER, LOT_NUMBER,QTY_IN,PART_NUMBER,PS_TYPE,P_TYPE,COMMENTS
			from 
				#INVENTORY_SUMMARY_TABLE
	OPEN @LOT_CURSOR
	FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID,@FAB_LOT_NUMBER,@VENDOR_LOT_NUMBER,@QTY_IN,@PART_NUMBER,@PS_TYPE,@P_TYPE,@COMMENTS
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF ( @PS_TYPE = 'Die Bank')
		BEGIN
			SET @DIE_BANK_QTY = @QTY_IN
			
			INSERT INTO @RESULTS ( LOT_ID,FAB_LOT_NUMBER,LOT_NUMBER,PART_NUMBER,DIE_BANK_QTY,P_TYPE,COMMENTS )
			VALUES (@LOT_ID,@FAB_LOT_NUMBER,@VENDOR_LOT_NUMBER,@PART_NUMBER,@DIE_BANK_QTY,@P_TYPE,@COMMENTS)
		END
		ELSE IF ( @PS_TYPE = 'Assembly')
		BEGIN
			SET @ASSEMBLY_QTY = @QTY_IN

			INSERT INTO @RESULTS ( LOT_ID,FAB_LOT_NUMBER,LOT_NUMBER,PART_NUMBER,ASSEMBLY_QTY,P_TYPE,COMMENTS)
			VALUES (@LOT_ID,@FAB_LOT_NUMBER,@VENDOR_LOT_NUMBER,@PART_NUMBER,@ASSEMBLY_QTY,@P_TYPE,@COMMENTS)
		END
		ELSE IF ( @PS_TYPE = 'TEST')
		BEGIN
			SET @TEST_FPO = @QTY_IN

			INSERT INTO @RESULTS ( LOT_ID,FAB_LOT_NUMBER,LOT_NUMBER,PART_NUMBER,TEST_QTY,P_TYPE,COMMENTS)
			VALUES (@LOT_ID,@FAB_LOT_NUMBER,@VENDOR_LOT_NUMBER,@PART_NUMBER,@TEST_FPO,@P_TYPE,@COMMENTS)
		END
		ELSE IF ( @PS_TYPE = 'FINISHED GOODS')
		BEGIN
			--SET @FINISHED_GOODS_QTY = @QTY_IN
			SELECT @FINISHED_GOODS_QTY = ISNULL(IL_QTY_BALANCE,IL_QTY_IN) FROM INVENTORY_LOT WHERE LOT_ID = @LOT_ID

			INSERT INTO @RESULTS ( LOT_ID,FAB_LOT_NUMBER,LOT_NUMBER,PART_NUMBER,FINISHED_GOODS_QTY,P_TYPE,COMMENTS)
			VALUES (@LOT_ID,@FAB_LOT_NUMBER,@VENDOR_LOT_NUMBER,@PART_NUMBER,@FINISHED_GOODS_QTY,@P_TYPE,@COMMENTS)
		END
			
			
			SET @DIE_BANK_QTY  	= NULL
			SET @ASSEMBLY_QTY  	= NULL
			SET @TEST_FPO 		= NULL
			SET @TEST_FT 		= NULL
			SET @FINISHED_GOODS_QTY = NULL

			SET @PART_NUMBER 	= NULL
			SET @QTY_IN 		= NULL
			SET @PS_TYPE 		= NULL
			SET @LOT_ID		= NULL
			SET @FAB_LOT_NUMBER	= NULL
			SET @VENDOR_LOT_NUMBER	= NULL
			SET @P_TYPE		= NULL
			SET @COMMENTS		= NULL
			
		FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID,@FAB_LOT_NUMBER,@VENDOR_LOT_NUMBER,@QTY_IN,@PART_NUMBER,@PS_TYPE,@P_TYPE,@COMMENTS
	END 	
	CLOSE @LOT_CURSOR
 	DEALLOCATE @LOT_CURSOR

	IF @REPORT_TYPE <> 'DETAIL'
	BEGIN
		SELECT 
			PART_NUMBER,SUM(DIE_BANK_QTY) AS DIE_BANK_QTY,SUM(ASSEMBLY_QTY) AS ASSEMBLY_QTY,SUM(TEST_QTY) AS TEST_QTY ,SUM(FINISHED_GOODS_QTY) AS FINISHED_GOODS_QTY
		FROM 
			@RESULTS 
		GROUP BY 
			PART_NUMBER
		ORDER BY 
			PART_NUMBER
	END
	ELSE
	BEGIN
		SELECT 
			FAB_LOT_NUMBER,LOT_NUMBER,PART_NUMBER,COMMENTS,P_TYPE,DIE_BANK_QTY,
			ASSEMBLY_QTY,TEST_QTY,FINISHED_GOODS_QTY
		FROM 
			@RESULTS
		ORDER BY
			P_TYPE,PART_NUMBER
	END
END
